package bean;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

public class ApplyBean {
    public ArrayList<Apply> adminArrayList;
    public HashMap<String,String> adminMap;
    public Connection connection;
    public PreparedStatement preparedStatement;
    public Statement statement;
    public ResultSet resultSet;

    public ApplyBean() {
        this.adminArrayList = new ArrayList<>();
        this.adminMap = new HashMap<>();
    }

    public ArrayList<Apply> getApplyArrayList() {
        return adminArrayList;
    }

    public HashMap<String, String> getApplyMap() {
        return adminMap;
    }

    public void link() {
        try {
            Class.forName("org.hsqldb.jdbc.JDBCDriver");
            Connection connection=DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public void loadApply(){
        String sql="select * from Apply";
        try {
            statement=connection.createStatement();
            resultSet=statement.executeQuery(sql);
            while(resultSet.next()){
                Apply admin=new Apply(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3),resultSet.getString(4),resultSet.getString(5));
                adminArrayList.add(admin);
                adminMap.put(resultSet.getString(3),resultSet.getString(1));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    public void insertApply(Apply admin){
        adminArrayList.add(admin);
        adminMap.put(admin.getId(),admin.getName());
        String sql="insert into Apply values(?,?,?,?,?)";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,admin.getName());
            preparedStatement.setString(2,admin.getId());
            preparedStatement.setString(3,admin.getItemName());
            preparedStatement.setString(4,admin.getResult());
            preparedStatement.setString(5,admin.getGrade());
            preparedStatement.execute();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

    public Apply selectApply(String name,String id){
        String sql="select * from Apply where name=? and id=?";
        int i=0;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setString(1,id);
            resultSet=preparedStatement.executeQuery();
            while(resultSet.next()){
                Apply admin = new Apply(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3),resultSet.getString(4),resultSet.getString(5));
                return admin;
            }

        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }

    public String[][] selectAward(String name){
        String sql="select apply.id,apply.name,apply.itemname,award.reward from Apply,Award where apply.id=award.id and apply.name=award.athlete and award.athlete=?";
        int i=0;
        String[][] a=new String[100][100];
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            resultSet=preparedStatement.executeQuery();
            while(resultSet.next()){
                    a[i][0]=resultSet.getString(1);
                    a[i][1]=resultSet.getString(2);
                    a[i][2]=resultSet.getString(3);
                    a[i][3]=resultSet.getString(4);
                    i++;
            }
            return a;
        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }

    public void updateApply(Apply apply){
        Apply a=selectApply(apply.getName(),apply.getId());
        adminArrayList.remove(a);
        adminArrayList.add(apply);
        String sql="update Apply set grade=?,result=? where id=? and name=?";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,apply.getGrade());
            preparedStatement.setString(2,apply.getResult());
            preparedStatement.setString(3,apply.getId());
            preparedStatement.setString(4,apply.getName());
            preparedStatement.execute();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }


}
